package com.ruoyi.gen.service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.TimeUnit;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.alibaba.druid.pool.DruidDataSource;
import com.ruoyi.common.core.exception.ServiceException;
import com.ruoyi.gen.domain.ExternalDbConfig;
import com.ruoyi.gen.domain.GenTable;
import com.ruoyi.gen.domain.GenTableColumn;

/**
 * 外部数据源服务
 * 
 * @author ruoyi
 */
@Service
public class ExternalDataSourceService {

    private static final Logger log = LoggerFactory.getLogger(ExternalDataSourceService.class);

    /** 临时数据源缓存 */
    private final ConcurrentHashMap<String, DruidDataSource> tempDataSources = new ConcurrentHashMap<>();

    /**
     * 测试数据库连接
     */
    public boolean testConnection(ExternalDbConfig config) {
        DruidDataSource dataSource = null;
        try {
            dataSource = createDataSource(config);
            try (Connection connection = dataSource.getConnection()) {
                return connection.isValid(config.getQueryTimeout());
            }
        } catch (Exception e) {
            log.error("测试数据库连接失败: {}", e.getMessage());
            return false;
        } finally {
            if (dataSource != null) {
                dataSource.close();
            }
        }
    }

    /**
     * 获取外部数据库表列表
     */
    public List<GenTable> getExternalDbTableList(ExternalDbConfig config, String tableName, String tableComment) {
        String cacheKey = generateCacheKey(config);
        DruidDataSource dataSource = tempDataSources.get(cacheKey);

        if (dataSource == null) {
            dataSource = createDataSource(config);
            tempDataSources.put(cacheKey, dataSource);

            // 设置定时清理任务
            scheduleCleanup(cacheKey, 10, TimeUnit.MINUTES);
        }

        List<GenTable> tables = new ArrayList<>();
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();

            String catalog = connection.getCatalog();
            String schema = config.getSchema();

            // 根据数据库类型调整查询参数
            if ("postgresql".equalsIgnoreCase(config.getDbType()) && schema == null) {
                schema = "public";
            }

            try (ResultSet rs = metaData.getTables(catalog, schema, null, new String[] { "TABLE" })) {
                while (rs.next()) {
                    String currentTableName = rs.getString("TABLE_NAME");
                    String currentTableComment = rs.getString("REMARKS");

                    // 过滤系统表和已导入的表
                    if (shouldIncludeTable(currentTableName, config.getDbType())) {
                        // 应用搜索过滤
                        if (matchesFilter(currentTableName, tableName) &&
                                matchesFilter(currentTableComment, tableComment)) {

                            GenTable table = new GenTable();
                            table.setTableName(currentTableName);
                            table.setTableComment(currentTableComment != null ? currentTableComment : "");

                            // 尝试获取时间字段，如果不存在则设为null
                            try {
                                table.setCreateTime(rs.getTimestamp("CREATE_TIME"));
                            } catch (SQLException e) {
                                table.setCreateTime(null);
                            }
                            try {
                                table.setUpdateTime(rs.getTimestamp("UPDATE_TIME"));
                            } catch (SQLException e) {
                                table.setUpdateTime(null);
                            }

                            tables.add(table);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            log.error("查询外部数据库表列表失败: {}", e.getMessage());
            throw new ServiceException("查询数据库表失败: " + e.getMessage());
        }

        return tables;
    }

    /**
     * 获取外部数据库表结构
     */
    public GenTable getExternalTableStructure(ExternalDbConfig config, String tableName) {
        String cacheKey = generateCacheKey(config);
        DruidDataSource dataSource = tempDataSources.get(cacheKey);

        if (dataSource == null) {
            throw new ServiceException("数据源连接已过期，请重新连接");
        }

        GenTable table = new GenTable();
        table.setTableName(tableName);

        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();

            // 获取表注释
            String catalog = connection.getCatalog();
            String schema = config.getSchema();

            try (ResultSet tableRs = metaData.getTables(catalog, schema, tableName, new String[] { "TABLE" })) {
                if (tableRs.next()) {
                    table.setTableComment(tableRs.getString("REMARKS"));
                }
            }

            // 这里可以进一步处理列信息，转换为GenTableColumn对象
            // 暂时返回基本的表信息，列信息的详细处理可以在后续版本中完善

        } catch (SQLException e) {
            log.error("获取表结构失败: {}", e.getMessage());
            throw new ServiceException("获取表结构失败: " + e.getMessage());
        }

        return table;
    }

    /**
     * 获取外部数据库表的字段信息
     */
    public List<GenTableColumn> getExternalTableColumns(ExternalDbConfig config, String tableName) {
        String cacheKey = generateCacheKey(config);
        DruidDataSource dataSource = tempDataSources.get(cacheKey);

        if (dataSource == null) {
            throw new ServiceException("数据源连接已过期，请重新连接");
        }

        List<GenTableColumn> columns = new ArrayList<>();
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();

            String catalog = connection.getCatalog();
            String schema = config.getSchema();

            // 根据数据库类型调整查询参数
            if ("postgresql".equalsIgnoreCase(config.getDbType()) && schema == null) {
                schema = "public";
            }

            try (ResultSet rs = metaData.getColumns(catalog, schema, tableName, null)) {
                while (rs.next()) {
                    GenTableColumn column = new GenTableColumn();

                    column.setColumnName(rs.getString("COLUMN_NAME"));
                    column.setColumnComment(rs.getString("REMARKS"));
                    column.setColumnType(rs.getString("TYPE_NAME"));
                    column.setSort(rs.getInt("ORDINAL_POSITION"));

                    // 判断是否为主键
                    column.setIsPk("0");
                    try (ResultSet pkRs = metaData.getPrimaryKeys(catalog, schema, tableName)) {
                        while (pkRs.next()) {
                            if (column.getColumnName().equals(pkRs.getString("COLUMN_NAME"))) {
                                column.setIsPk("1");
                                break;
                            }
                        }
                    }

                    // 判断是否可为空
                    int nullable = rs.getInt("NULLABLE");
                    column.setIsRequired(
                            nullable == DatabaseMetaData.columnNoNulls && !"1".equals(column.getIsPk()) ? "1" : "0");

                    // 判断是否自增
                    String isAutoIncrement = rs.getString("IS_AUTOINCREMENT");
                    column.setIsIncrement("YES".equalsIgnoreCase(isAutoIncrement) ? "1" : "0");

                    // 获取完整的列类型信息（包括长度、精度等）
                    int columnSize = rs.getInt("COLUMN_SIZE");
                    int decimalDigits = rs.getInt("DECIMAL_DIGITS");
                    String fullColumnType = buildFullColumnType(rs.getString("TYPE_NAME"), columnSize, decimalDigits);
                    column.setColumnType(fullColumnType);

                    columns.add(column);
                }
            }
        } catch (SQLException e) {
            log.error("获取外部数据库表字段信息失败: {}", e.getMessage());
            throw new ServiceException("获取表字段信息失败: " + e.getMessage());
        }

        return columns;
    }

    /**
     * 构建完整的列类型信息
     */
    private String buildFullColumnType(String typeName, int columnSize, int decimalDigits) {
        if (typeName == null) {
            return "";
        }

        String lowerTypeName = typeName.toLowerCase();

        // 对于需要长度的类型
        if (lowerTypeName.contains("char") || lowerTypeName.contains("text") ||
                lowerTypeName.contains("binary") || lowerTypeName.contains("blob")) {
            if (columnSize > 0) {
                return typeName + "(" + columnSize + ")";
            }
        }
        // 对于数值类型
        else if (lowerTypeName.contains("decimal") || lowerTypeName.contains("numeric")) {
            if (columnSize > 0 && decimalDigits > 0) {
                return typeName + "(" + columnSize + "," + decimalDigits + ")";
            } else if (columnSize > 0) {
                return typeName + "(" + columnSize + ")";
            }
        }
        // 对于整数类型
        else if (lowerTypeName.contains("int") || lowerTypeName.contains("bigint")) {
            if (columnSize > 0 && columnSize != 10 && columnSize != 11) { // 默认长度不显示
                return typeName + "(" + columnSize + ")";
            }
        }

        return typeName;
    }

    /**
     * 清理指定的数据源连接
     */
    public void cleanupDataSource(String cacheKey) {
        DruidDataSource dataSource = tempDataSources.remove(cacheKey);
        if (dataSource != null) {
            dataSource.close();
            log.info("清理临时数据源: {}", cacheKey);
        }
    }

    /**
     * 清理所有临时数据源
     */
    public void cleanupAllDataSources() {
        tempDataSources.forEach((key, dataSource) -> {
            dataSource.close();
            log.info("清理临时数据源: {}", key);
        });
        tempDataSources.clear();
    }

    /**
     * 创建数据源
     */
    private DruidDataSource createDataSource(ExternalDbConfig config) {
        DruidDataSource dataSource = new DruidDataSource();

        dataSource.setUrl(config.getJdbcUrl());
        dataSource.setUsername(config.getUsername());
        dataSource.setPassword(config.getPassword());
        dataSource.setDriverClassName(config.getDriverClassName());

        // 连接池配置
        dataSource.setInitialSize(1);
        dataSource.setMinIdle(1);
        dataSource.setMaxActive(5);
        dataSource.setMaxWait(config.getConnectTimeout() * 1000L);
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        dataSource.setMinEvictableIdleTimeMillis(300000);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);

        try {
            dataSource.init();
        } catch (SQLException e) {
            throw new ServiceException("初始化数据源失败: " + e.getMessage());
        }

        return dataSource;
    }

    /**
     * 生成缓存键
     */
    private String generateCacheKey(ExternalDbConfig config) {
        return String.format("%s_%s_%s_%s_%s",
                config.getDbType(), config.getHost(), config.getPort(),
                config.getDatabase(), config.getUsername());
    }

    /**
     * 判断是否应该包含该表
     */
    private boolean shouldIncludeTable(String tableName, String dbType) {
        if (tableName == null)
            return false;

        String lowerTableName = tableName.toLowerCase();

        // 排除系统表
        if (lowerTableName.startsWith("sys_") ||
                lowerTableName.startsWith("information_schema") ||
                lowerTableName.startsWith("performance_schema") ||
                lowerTableName.startsWith("mysql") ||
                lowerTableName.startsWith("qrtz_") ||
                lowerTableName.startsWith("gen_")) {
            return false;
        }

        return true;
    }

    /**
     * 检查是否匹配过滤条件
     */
    private boolean matchesFilter(String value, String filter) {
        if (filter == null || filter.trim().isEmpty()) {
            return true;
        }
        if (value == null) {
            return false;
        }
        return value.toLowerCase().contains(filter.toLowerCase());
    }

    /**
     * 定时清理任务
     */
    private void scheduleCleanup(String cacheKey, long delay, TimeUnit timeUnit) {
        // 这里可以使用Spring的@Scheduled或者线程池来实现定时清理
        // 简单实现：使用单独线程
        new Thread(() -> {
            try {
                timeUnit.sleep(delay);
                cleanupDataSource(cacheKey);
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
            }
        }).start();
    }
}
